package cn.edu.buaa.cnsatm.traffic_big_data_server.integrated.dao;

import cn.edu.buaa.cnsatm.traffic_big_data_server.integrated.model.RoadStatusModel;
import cn.edu.buaa.cnsatm.traffic_big_data_server.util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class RoadStatusDAO {
    public static ResultSet getTopKRoadEvent(int k){
        /**
         * @author: LX
         * @methodName: getAllRoadEvent
         * @description: 获得所有除气象外的交通事件，每个大类取前K个
         * @data: 2019-11-05
         **/
        String sql = "select * from(\n" +
                " select \n" +
                " block_description,road_type,block_reason,update_time,block_title,\n" +
                " rank()over(partition by road_type order by update_time desc) as NORow  \n" +
                "  from road_status WHERE block_reason != '气象'\n" +
                "  GROUP BY  block_description,road_type,block_reason,update_time,block_title) v\n" +
                "  where NORow <= " + String.valueOf(k);
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();
            resultSet= stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static String insertRoadStatus(RoadStatusModel roadStatusModel, boolean isInsert) throws SQLException {
        /**
         * @author: LX
         * @methodName: insertRoadStatus
         * @description: 将综合交通路况信息插入到road_status表中
         * @data: 2019-11-02
         **/
        int insertNum = 0;
        /*构造函数做了小处理，sql语句不再需要单引号了*/
        String sql = "INSERT into road_status (road_type, block_reason, block_description, block_geom, block_start_time, block_end_time_plan, block_end_time_real, update_time, block_title) " +
                "values(" + roadStatusModel.getRoad_type() + ", " + roadStatusModel.getBlock_reason() + ", "  +
                roadStatusModel.getBlock_description() + ", " + roadStatusModel.getBlock_geom() + ", " +
                roadStatusModel.getBlock_start_time() + ", " + roadStatusModel.getBlock_end_time_plan() + ", " +
                roadStatusModel.getBlock_end_time_real() + ", " + roadStatusModel.getUpdate_time() +  "," + roadStatusModel.getBlock_title() + ");";
        if(isInsert == true){
            Statement stmt = null;
            try {
                stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
                insertNum = stmt.executeUpdate(sql);//执行Sql查询语句
                stmt.close();
                //System.out.print(insertNum);
            } catch (SQLException e) {
                //System.out.println(sql);
                e.printStackTrace();
            }
        }
        else{
            return sql;
        }

        return null;
    }
    public static ResultSet getAllRoadAccident(){
        /**
         * @author: LX
         * @methodName: getTopKRoadAccident
         * @description: 查询前k条（每样最多取k条）综合交通事件
         * @data: 2019-11-05
         **/
        String sql = "SELECT road_type, block_reason, block_description, block_start_time, block_title FROM road_status GROUP BY road_type, block_reason ORDER BY block_start_time DESC";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();
            resultSet= stmt.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
